package com.ojoin.trade.common.utils.excel;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import com.ojoin.trade.common.utils.LogUtils;

/**
 * 抽象Excel2007读取器，excel2007的底层数据结构是xml文件，采用SAX的事件驱动的方法解析
 * xml，需要继承DefaultHandler，在遇到文件内容时，事件会触发，这种做法可以大大降低 内存的耗费，特别使用于大数据量的文件。
 * 
 */
public class Excel2007Readers extends DefaultHandler {

	private static Logger log = LoggerFactory.getLogger(Excel2007Readers.class);

	static class xssfDataType {
		public static final xssfDataType BOOL = new xssfDataType();
		public static final xssfDataType ERROR = new xssfDataType();
		public static final xssfDataType FORMULA = new xssfDataType();
		public static final xssfDataType INLINESTR = new xssfDataType();
		public static final xssfDataType SSTINDEX = new xssfDataType();
		public static final xssfDataType NUMBER = new xssfDataType();
		public static final xssfDataType DATE = new xssfDataType();

		protected xssfDataType() {
		}
	}

	private List rowlist = new ArrayList();
	private int sheetIndex = -1;

	private String sheetName;
	private Map data = new HashMap();
	/**
	 * Number of columns to read starting with leftmost
	 */
	private StylesTable stylesTable;
	// 当前行
	private int curRow = 0;
	// 当前列
	private int curCol = 0;
	/**
	 * Table with unique strings
	 */
	private SharedStringsTable sharedStringsTable;
	// Set when V start element is seen
	private boolean vIsOpen;

	// Set when cell start element is seen;
	// used when cell close element is seen.
	private xssfDataType nextDataType;

	// Used to format numeric cell values.
	private short formatIndex;
	private String formatString;
	private final DataFormatter formatter;
	private int thisColumn = -1;
	// The last column printed to the output stream
	private int lastColumnNumber = -1;
	// Gathers characters as they are seen.
	private StringBuffer value;

	public Excel2007Readers() {
		this.value = new StringBuffer();
		this.nextDataType = xssfDataType.NUMBER;
		this.formatter = new DataFormatter();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String,
	 * java.lang.String, java.lang.String, org.xml.sax.Attributes)
	 */
	public void startElement(String uri, String localName, String name,
			Attributes attributes) throws SAXException {

		if ("inlineStr".equals(name) || "v".equals(name)) {
			vIsOpen = true;
			value.setLength(0);
		}
		// c => cell
		else if ("c".equals(name)) {
			// Get the cell reference
			String r = attributes.getValue("r");
			int firstDigit = -1;
			for (int c = 0; c < r.length(); ++c) {
				if (Character.isDigit(r.charAt(c))) {
					firstDigit = c;
					break;
				}
			}
			thisColumn = nameToColumn(r.substring(0, firstDigit));

			// Set up defaults.
			this.nextDataType = xssfDataType.NUMBER;
			this.formatIndex = -1;
			this.formatString = null;
			String cellType = attributes.getValue("t");
			String cellStyleStr = attributes.getValue("s");
			if ("b".equals(cellType))
				nextDataType = xssfDataType.BOOL;
			else if ("e".equals(cellType))
				nextDataType = xssfDataType.ERROR;
			else if ("inlineStr".equals(cellType))
				nextDataType = xssfDataType.INLINESTR;
			else if ("s".equals(cellType))
				nextDataType = xssfDataType.SSTINDEX;
			else if ("str".equals(cellType))
				nextDataType = xssfDataType.FORMULA;
			else if (cellStyleStr != null) {
				int styleIndex = Integer.parseInt(cellStyleStr);
				XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
				this.formatIndex = style.getDataFormat();
				this.formatString = style.getDataFormatString();
				if ("m/d/yy" == formatString) {
					nextDataType = xssfDataType.DATE;
					formatString = "yyyy-MM-dd";
				}

				if (this.formatString == null)
					this.formatString = BuiltinFormats
							.getBuiltinFormat(this.formatIndex);
			}
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String,
	 * java.lang.String, java.lang.String)
	 */
	public void endElement(String uri, String localName, String name)
			throws SAXException {

		String thisStr = null;
		// v => contents of a cell
		if ("v".equals(name)) {
			if (nextDataType == xssfDataType.BOOL) {
				char first = value.charAt(0);
				thisStr = first == '0' ? "FALSE" : "TRUE";
			} else if (nextDataType == xssfDataType.ERROR) {
				thisStr = "\"ERROR:" + value.toString() + '"';
			} else if (nextDataType == xssfDataType.FORMULA) {
				thisStr = value.toString();
			} else if (nextDataType == xssfDataType.INLINESTR) {
				XSSFRichTextString rtsi = new XSSFRichTextString(
						value.toString());
				thisStr = '"' + rtsi.toString() + '"';
			} else if (nextDataType == xssfDataType.SSTINDEX) {
				String sstIndex = value.toString();
				try {
					int idx = Integer.parseInt(sstIndex);
					XSSFRichTextString rtss = new XSSFRichTextString(
							sharedStringsTable.getEntryAt(idx));
					thisStr = rtss.toString();
				} catch (NumberFormatException ex) {
					thisStr = value.toString();
				}
			} else if (nextDataType == xssfDataType.NUMBER) {
				String n = value.toString();
				if (this.formatString != null) {
					thisStr = formatter.formatRawCellContents(
							Double.parseDouble(n), this.formatIndex,
							this.formatString);
				} else {
					if (n.indexOf(".") > 0) {
						thisStr = new DecimalFormat("#.##########")
								.format(Double.valueOf(n));
					} else {
						thisStr = n;
					}
				}
			} else if (nextDataType == xssfDataType.DATE) {
				thisStr = formatter.formatRawCellContents(
						Double.parseDouble(value.toString()), formatIndex,
						formatString);
			} else {
				thisStr = ExcelReaderUtil.EMPTY_CELL;
			}

			for (int i = lastColumnNumber; i < thisColumn - 1; ++i) {
				rowlist.add(curCol, ExcelReaderUtil.EMPTY_CELL);
				curCol++;
			}

			rowlist.add(curCol, thisStr.toString());
			curCol++;
			// Update column
			if (thisColumn > -1)
				lastColumnNumber = thisColumn;

		} else if ("row".equals(name)) {
			try {
				rowReader.getRows(sheetIndex, sheetName, curRow, rowlist);
			} catch (Exception e) {
				LogUtils.error(log, e,"解析出错--sheetIndex" + sheetIndex
						+ "--sheetName:" + sheetName + "--curRow" + curRow
						+ "--rowlist"); 
				throw e;
			} 
			rowlist.clear();
			curRow++;
			curCol = 0;
			lastColumnNumber = -1;
		}

	}

	/**
	 * Captures characters only if a suitable element is open. Originally was
	 * just "v"; extended for inlineStr also.
	 */
	public void characters(char[] ch, int start, int length)
			throws SAXException {
		if (vIsOpen)
			value.append(ch, start, length);
	}

	/**
	 * Converts an Excel column name like "C" to a zero-based index.
	 * 
	 * @param name
	 * @return Index corresponding to the specified name
	 */
	private int nameToColumn(String name) {
		int column = -1;
		for (int i = 0; i < name.length(); ++i) {
			int c = name.charAt(i);
			column = (column + 1) * 26 + c - 'A';
		}
		return column;
	}

	private IRowReader rowReader;

	public void setRowReader(IRowReader rowReader) {
		this.rowReader = rowReader;
	}

	/**
	 * Parses and shows the content of one sheet using the specified styles and
	 * shared-strings tables.
	 * 
	 * @param styles
	 * @param strings
	 * @param sheetInputStream
	 */
	public void processSheet(StylesTable styles, SharedStringsTable strings,
			InputStream sheetInputStream) throws IOException,
			ParserConfigurationException, SAXException {
		sheetIndex++;
		curRow = 0;// 切换也签，新行号开始
		InputSource sheetSource = new InputSource(sheetInputStream);
		// XMLReader sheetParser =
		// XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
		XMLReader sheetParser = XMLReaderFactory.createXMLReader();
		sheetParser.setContentHandler(this);
		sheetParser.parse(sheetSource);
	}

	/**
	 * Initiates the processing of the XLS workbook file to CSV.
	 * 
	 * @throws IOException
	 * @throws OpenXML4JException
	 * @throws ParserConfigurationException
	 * @throws SAXException
	 */
	public void process(InputStream filename) throws IOException,
			OpenXML4JException, ParserConfigurationException, SAXException {

		OPCPackage pkg = OPCPackage.open(filename);
		XSSFReader xssfReader = new XSSFReader(pkg);
		sharedStringsTable = xssfReader.getSharedStringsTable();
		stylesTable = xssfReader.getStylesTable();
		XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
				.getSheetsData();
		while (iter.hasNext()) {
			InputStream stream = iter.next();
			sheetName = iter.getSheetName();

			processSheet(stylesTable, sharedStringsTable, stream);
			stream.close();
		}
	}

}
